<?php 
/**
 * Excel操作类
 * @Author    紫梦
 * @DateTime  2017-12-14
 * @copyright 响响租车
 * @version   1.0
 * @UpdateTime 2017-12-14
 */
namespace app\common\controller;
use PHPExcel_IOFactory;
use PHPExcel;




class Execl
{
	public function index()
	{
		$PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle("demo");
        $PHPSheet->setCellValue("A1","姓名")->setCellValue("B1","分数");
        $PHPSheet->setCellValue("A2","张三")->setCellValue("B2","2121");
        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="表单数据.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");
	}

    public function readerBase($file)
    {
        $PHPExcel = PHPExcel_IOFactory::load($file);   //建立reader对象  
        $sheet = $PHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumm = $sheet->getHighestColumn(); // 取得总列数
        $asciiStart = 65;
        if(strlen($highestColumm)>1){
            return false;
        }
        $asciiEnd = ord($highestColumm);
        for ($i=65; $i <=$asciiEnd ; $i++) { 
            for ($j=1; $j <=$highestRow ; $j++) { 
                
               $data[$j-1][] = $sheet->getCell(chr($i).$j)->getValue();
                
            }
           
        }
        return $data;
    }

    public function writerBase($data,$title)
    {
        $PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle($title);
        $asciiStart = 65;
        foreach ($data as $key => $value) {
            $key++;
            foreach ($value as $row => $colum) {
                
                $PHPSheet->setCellValue(chr($asciiStart).$key,$colum);  
                $asciiStart++;
            }

        }

        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="'.$title.'.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");exit;
    }

    /**
     * 导出车辆基础数据excel
     */
    
    public function carExcel($brand,$company,$agency)
    {
        // var_dump($arr,$brand);
        $PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle('车辆基础数据');
         //填入表头
        $PHPExcel->getActiveSheet()->setCellValue('A1', '车牌号码');
        $PHPExcel->getActiveSheet()->setCellValue('B1', '车架号');
        $PHPExcel->getActiveSheet()->setCellValue('C1', '发动机号');
        $PHPExcel->getActiveSheet()->setCellValue('D1', '车辆型号');
        $PHPExcel->getActiveSheet()->setCellValue('E1', '汽车品牌');
        $PHPExcel->getActiveSheet()->setCellValue('F1', '车辆类型');
        $PHPExcel->getActiveSheet()->setCellValue('G1', '供应商ID');
        $PHPExcel->getActiveSheet()->setCellValue('H1', '物联网卡号');
        $PHPExcel->getActiveSheet()->setCellValue('I1', '物联网卡序列号');
        $PHPExcel->getActiveSheet()->setCellValue('J1', '车载设备编号');
        $PHPExcel->getActiveSheet()->setCellValue('K1', '副供应商ID');
        $PHPExcel->getActiveSheet()->setCellValue('L1', '副物联网卡号');
        $PHPExcel->getActiveSheet()->setCellValue('M1', '副物联网卡序列号');
        $PHPExcel->getActiveSheet()->setCellValue('N1', '副车载设备编号');
        $PHPExcel->getActiveSheet()->setCellValue('O1', '租赁类型');
        $PHPExcel->getActiveSheet()->setCellValue('P1', '机车所有人');
        $PHPExcel->getActiveSheet()->setCellValue('Q1', '所属加盟商');

        for ($i=2; $i <500 ; $i++) { 
            
          $PHPExcel->getActiveSheet()->getCell('E'.$i)->getDataValidation()
            -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('请选择')
            -> setError('您输入的值不在下拉框列表内')
            -> setPromptTitle('汽车品牌')
            -> setFormula1('"'.join(',',array_column($brand,'name')).'"');
        }

        for ($i=2; $i <500 ; $i++) { 
            
          $PHPExcel->getActiveSheet()->getCell('F'.$i)->getDataValidation()
            -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('请选择')
            -> setError('您输入的值不在下拉框列表内')
            -> setPromptTitle('车辆类型')
            -> setFormula1('"小型轿车,SUV,中巴"');
        }
         
        for ($i=2; $i <500 ; $i++) { 
            
          $PHPExcel->getActiveSheet()->getCell('G'.$i)->getDataValidation()
            -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('请选择')
            -> setError('您输入的值不在下拉框列表内')
            -> setPromptTitle('供应商ID')
            -> setFormula1('"博鹭途,唯特迅"');
        }
        for ($i=2; $i <500 ; $i++) { 
            
          $PHPExcel->getActiveSheet()->getCell('K'.$i)->getDataValidation()
            -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('请选择')
            -> setError('您输入的值不在下拉框列表内')
            -> setPromptTitle('副供应商ID')
            -> setFormula1('"博鹭途,唯特迅"');
        }
        for ($i=2; $i <500 ; $i++) { 
            
          $PHPExcel->getActiveSheet()->getCell('O'.$i)->getDataValidation()
            -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('请选择')
            -> setError('您输入的值不在下拉框列表内')
            -> setPromptTitle('租赁类型')
            -> setFormula1('"分时租赁,企业用车,共享用车,长租车辆,自测试用,上牌整备,整备待租"');
        }
        for ($i=2; $i <500 ; $i++) { 
            
          $PHPExcel->getActiveSheet()->getCell('P'.$i)->getDataValidation()
            -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('请选择')
            -> setError('您输入的值不在下拉框列表内')
            -> setPromptTitle('机车所有人')
            -> setFormula1('"'.join(',',array_column($company,'cp_name')).'"');
        }

        for ($i=2; $i <500 ; $i++) { 
            
          $PHPExcel->getActiveSheet()->getCell('Q'.$i)->getDataValidation()
            -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('请选择')
            -> setError('您输入的值不在下拉框列表内')
            -> setPromptTitle('所属加盟商')
            -> setFormula1('"'.join(',',array_column($agency,'name')).'"');
        }
        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="车辆基础数据.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");exit;

    }
    

}